﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace org.maquech.CEeL.Core.Infrastructure {
    public sealed class Building : PersistentObject {
        #region Fields and Properties

        /// <summary>
        /// The Building's name field.
        /// </summary>
        private string name;

        /// <summary>
        /// The Building's name.
        /// </summary>
        public string Name {
            get {
                return this.name;
            }
            set {
                if ( this.name != value )
                    modifiedDBFields.Add( "name" );
                this.name = value;
            }
        }
        
        /// <summary>
        /// The Building's name abbreviation field.
        /// </summary>
        private string abbreviation;

        /// <summary>
        /// The Building's name abbreviation.
        /// </summary>
        public string Abbreviation {
            get {
                return this.abbreviation;
            }
            set {
                if ( this.abbreviation != value )
                    modifiedDBFields.Add( "abbreviation" );
                this.abbreviation = value;
            }
        }

        /// <summary>
        /// The Building's location field.
        /// </summary>
        private string location;

        /// <summary>
        /// The Building's location.
        /// </summary>
        public string Location {
            get {
                return this.location;
            }
            set {
                if ( this.location != value )
                    modifiedDBFields.Add( "location" );
                this.location = value;
            }
        }

        /// <summary>
        /// Checks if the current instance is valid to be stored in the database.
        /// </summary>
        public override bool IsValid {
            get {
                return  !(this.name == null) && (this.name.Trim() != "")
                    && ( !( this.abbreviation == null ) && ( this.abbreviation.Trim( ) != "" ) );
            }
        }

        /// <summary>
        /// Gets a list of Services in the current Building.
        /// </summary>
        public List<Service> Services {
            get {
                List<Service> servs = new List<Service>( );
                Database db = DatabaseFactory.CreateDatabase( );
                DbCommand dbCommand;
                string sqlCommand = "SELECT s.id, s.name, s.abbreviation, s.description FROM services AS s, building_services as bs"
                    + " WHERE bs.building_id = @id AND bs.service_id = s.id ORDER BY s.name";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    while ( rdr.Read( ) ) {
                        servs.Add( new Service( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( string ) rdr [ "description" ] ) );
                    }
                    rdr.Close( );
                }
                return servs;
            }
        }
        #endregion

        #region Constructors
        public Building ( ) {
            this.id = -1;
            this.name = "";
            this.abbreviation = "";
            this.location = "";
            this.isNew = true;
        }

        public Building ( int id, string name, string abbreviation, string location ) {
            this.id = id;
            this.name = name;
            this.abbreviation = abbreviation;
            this.location = location;
            this.isNew = ( id == -1 );
        }

        public Building ( string name, string abbreviation, string location )
            : this( -1, name, abbreviation, location ) {
        }
        #endregion

        #region Methods

        #region System.Object Methods
        /// <summary>
        /// Checks if the object is equal to the current instance.
        /// </summary>
        /// <param name="obj">The object to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public override bool Equals ( object obj ) {
            if ( obj == null ) {
                return false;
            }
            Building b = obj as Building;
            if ( ( object ) b == null ) {
                return false;
            }
            return ( this.name == b.name )
                && ( this.abbreviation == b.abbreviation )
                && ( this.location == b.location );
        }

        /// <summary>
        /// Checks if the Building is equal to the current instance.
        /// </summary>
        /// <param name="b">The Builduing to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public bool Equals ( Building b ) {
            if ( ( object ) b == null ) {
                return false;
            }
            return ( this.name == b.name )
                && ( this.abbreviation == b.abbreviation )
                && ( this.location == b.location );
        }

        public override int GetHashCode ( ) {
            return id.GetHashCode( )
                ^ this.name.GetHashCode( )
                ^ this.abbreviation.GetHashCode( )
                ^ this.location.GetHashCode( );
        }

        public override string ToString ( ) {
            StringBuilder bldr = new StringBuilder( );
            object [ ] objArr = new object [ ] { this.id, this.abbreviation, this.name, this.location };
            bldr.AppendFormat( "[{0} :  {1}] : {2} @ {3}", objArr );
            return bldr.ToString( );
        }
        #endregion

        /// <summary>
        /// Saves the current instance to the database.
        /// </summary>
        /// <returns><code>True</code> if the object was saved to the database, <code>false</code> otherwise.</returns>
        public override bool Save ( ){
            bool saved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;

            try {
                sqlCommand = "SELECT count(*) = 0 as exists FROM buildings WHERE id = @id OR name ILIKE @name";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                isNew = ( bool ) db.ExecuteScalar( dbCommand );
                if ( isNew ) {
                    sqlCommand = "SELECT nextval('buildings_id_seq')";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    this.id = ( int ) db.ExecuteScalar( dbCommand );

                    sqlCommand = "INSERT INTO buildings ( id, name, abbreviation, location )"
                       + " VALUES ( @id, @name, @abbreviation, @location )";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@abbreviation", DbType.String, this.abbreviation );
                    db.AddInParameter( dbCommand, "@location", DbType.String, this.location );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else if ( this.modifiedDBFields.Count > 0 ) {
                    sqlCommand = "UPDATE buildings SET name = @name, abbreviation = @abbreviaton, location = @location"
                     + " WHERE id = @id";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                    db.AddInParameter( dbCommand, "@abbreviation", DbType.String, this.abbreviation );
                    db.AddInParameter( dbCommand, "@location", DbType.String, this.location );
                    saved = db.ExecuteNonQuery( dbCommand ) > 0;
                }
                else {
                    //No update or insert needed
                    saved = true;
                }
            }
            catch ( Exception e ) {
                saved = false;
                this.errorMessages.Add( "There was an error saving the Building: " + e.Message );
            }
            return saved;
        }

        /// <summary>
        /// Deletes the current object from the database.
        /// </summary>
        /// <returns><code>True</code> if the object was deleted from the database, <code>false</code> otherwise.</returns>
        public override bool Delete ( ) {
            bool deleted = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            try {
                sqlCommand = "DELETE FROM buildings WHERE id = @id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                deleted = db.ExecuteNonQuery( dbCommand ) >= 0;
            }
            catch ( Exception e ) {
                deleted = false;
                this.errorMessages.Add( "There was an error saving the Building: " + e.Message );
            }
            return deleted;
        }


        #region Load Methods
        /// <summary>
        /// Loads the Building with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Buildings' id.</param>
        /// <returns>A Building object with it's information or a null Building object if the <code>id</code> was not present in the database.</returns>
        public static Building Load ( int id ) {
            Building b = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT name, abbreviation, location FROM buildings WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        b = new Building( id, ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], rdr [ "location" ] as string  );
                        b.modifiedDBFields.Clear( );
                        b.ClearMessages( );
                        b.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                b.errorMessages.Add( string.Format( "There was an error loading the Building with ID {0}: {1}", id.ToString( ), e.Message ) );
            }
            return b;
        }

        /// <summary>
        /// Loads the Building with the given <code>name</code>.
        /// </summary>
        /// <param name="id">The Buildings' name.</param>
        /// <returns>A Building object with it's information or a null Building object if the <code>name</code> was not present in the database.</returns>
        public static Building LoadByName ( string name ) {
            Building b = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT id, name, abbreviation, location FROM buildings WHERE name ILIKE @name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        b = new Building( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( string ) rdr [ "location" ] );
                        b.modifiedDBFields.Clear( );
                        b.ClearMessages( );
                        b.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                b.errorMessages.Add( string.Format( "There was an error loading the Building {0}: {1}", name, e.Message ) );
            }
            return b;
        }

        /// <summary>
        /// Checks the database for existence of the Building with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Buildings' id.</param>
        /// <returns><code>True</code> if the building with the given <code>id</code>exists, <code>false</code> otherwise.</returns>
        public static bool Exists ( int id ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM buildings WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Checks the database for existence of the Building with the given <code>name</code>.
        /// </summary>
        /// <param name="name">The Buildings' name</param>
        /// <returns><code>True</code> if the building with the given <code>name</code>exists, <code>false</code> otherwise.</returns>
        public static bool ExistsByName ( string name ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM buildings WHERE name ILIKE @name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@name", DbType.String, name );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        /// <summary>
        /// Loads all of the Buildings in the database to a list.
        /// </summary>
        /// <returns>A List containing all of the Buildings on the database.</returns>
        public static List<Building> LoadAll ( ) {
            List<Building> bl = new List<Building>( );
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT id, name, abbreviation, location FROM buildings ORDER BY name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                while ( rdr.Read( ) ) {
                    bl.Add( new Building( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], ( string ) rdr [ "abbreviation" ], ( string ) rdr [ "location" ] ) );
                }
                rdr.Close( );
            }
            return bl;
        }
        #endregion

        #endregion
    }
}
